/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ipcc.utility.model;

import com.ipcc.utility.entity.Call;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.ResourceBundle;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class CallModel {
    public static ArrayList<Call> searchListCallByCondition(String soGoi,String soNhan,String tuNgay,String denNgay) throws Exception{
        ArrayList<Call> list = new ArrayList<Call>();
        Connection cn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        if(soGoi.equals("") || soGoi.equalsIgnoreCase("")) soGoi = null;
        if(soNhan.equals("") || soNhan.equalsIgnoreCase("")) soNhan = null;
        try {                            
                String str = "select caller_id_number,destination_number,TO_CHAR(start_stamp,'dd-mm-yyyy hh24:mi:ss'),TO_CHAR(end_stamp,'dd-mm-yyyy hh24:mi:ss'),billsec,nvl(recording_file_path,' '),hangup_cause "
                + " from fs_cdr "
                + " where (destination_number like '%'||?||'%' or ? is null) "                
                + " and (caller_id_number like '%'||?||'%' or ? is null)"
                + checkTuNgay(tuNgay)
                + checkDenngay(denNgay)
                + " order by start_stamp ";
                
                cn = AppServer.getConnection();
                pst = cn.prepareStatement(str);
                
                pst.setString(1, soNhan);
                pst.setString(2, soNhan);
                pst.setString(3, soGoi);
                pst.setString(4, soGoi);
                if(!tuNgay.equals("")) pst.setString(5, tuNgay);
                if(!denNgay.equals("") && !tuNgay.equals("")) pst.setString(6, denNgay);
                if(!denNgay.equals("") && tuNgay.equals("")) pst.setString(5, denNgay);
                                                
                rs = pst.executeQuery();
                
                while (rs.next()) {
                    Call call = new Call();
                    call.setSoGoi(rs.getString(1));
                    call.setSoNhan(rs.getString(2));
                    call.setNgaygioGoi(rs.getString(3));
                    call.setThoigianKetthuc(rs.getString(4));
                    call.setThoiluong(Integer.parseInt(rs.getString(5)));
                    call.setGhiam(rs.getString(6));
                    call.setTrangthai(getTrangthai(rs.getString(7)));
                    list.add(call);
                }
                int i = list.size();                
                return list;
            }
         finally {
            Database.closeObject(rs);
            Database.closeObject(pst);
            Database.closeObject(cn);
        }     
    }
    public static String checkTuNgay(String tuNgay){
        if(tuNgay == null || tuNgay.equalsIgnoreCase("")) tuNgay = "";
        if(tuNgay.equals("")) return "";
        return " and start_stamp >= TO_DATE(?,'dd-mm-yyyy') ";
    }
    
    public static String checkDenngay(String denNgay){
        if(denNgay == null || denNgay.equalsIgnoreCase("")) denNgay = "";
        if(denNgay.equals("")) return "";
        return " and start_stamp <= TO_DATE(?,'dd-mm-yyyy') + 1 ";
    }        
    public static String getTrangthai(String key){
        ResourceBundle resource = ResourceBundle.getBundle("call-hangup-cause");
        return resource.getString(key);
    }
}
